import os
import sqlite3
from datetime import datetime
from utils.log_handler import logger

class DatabaseHandler:
    """数据库处理器类 - 提供统一的数据库操作接口"""
    _instance = None
    DB_PATH = None

    @classmethod
    def get_instance(cls):
        """获取单例实例"""
        if cls._instance is None:
            cls._instance = cls()
        return cls._instance

    def __init__(self):
        """初始化数据库连接设置"""
        if DatabaseHandler.DB_PATH is not None:
            return
            
        # Windows 数据库路径：根目录下的 gantt_project.db（适配不同用户目录）
        base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        DatabaseHandler.DB_PATH = os.path.join(base_dir, "gantt_project.db")
        # 确保路径分隔符为 Windows 格式
        DatabaseHandler.DB_PATH = DatabaseHandler.DB_PATH.replace("/", "\\")
        
        # 初始化数据库表
        self.init_db()

    def get_connection(self):
        """获取数据库连接 - 启用外键+行工厂"""
        try:
            conn = sqlite3.connect(DatabaseHandler.DB_PATH)
            conn.row_factory = sqlite3.Row  # 支持按列名取值
            conn.execute("PRAGMA foreign_keys = ON")  # 启用外键（级联删除）
            conn.execute("PRAGMA encoding = 'UTF-8'")  # Windows 中文编码适配
            return conn
        except sqlite3.Error as e:
            logger.error(f"Windows 数据库连接失败：{str(e)}（路径：{DatabaseHandler.DB_PATH}）")
            raise  # 抛出异常，让上层处理

    def init_db(self):
        """初始化数据库：创建项目/任务/日志表（Windows 首次启动自动执行）"""
        with self.get_connection() as conn:
            try:
                # 1. 项目表（projects）
                conn.execute('''
                CREATE TABLE IF NOT EXISTS projects (
                    project_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    project_name TEXT NOT NULL,
                    start_date TEXT NOT NULL,
                    end_date TEXT NOT NULL,
                    description TEXT,
                    create_time TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
                    update_time TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
                )
                ''')

                # 2. 任务表（tasks）：外键关联项目，级联删除
                conn.execute('''
                CREATE TABLE IF NOT EXISTS tasks (
                    task_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    project_id INTEGER NOT NULL,
                    task_name TEXT NOT NULL,
                    priority TEXT NOT NULL DEFAULT '中',
                    owner TEXT NOT NULL DEFAULT '未指定',
                    start_date TEXT NOT NULL,
                    end_date TEXT NOT NULL,
                    status TEXT NOT NULL DEFAULT '未开始',
                    progress INTEGER NOT NULL DEFAULT 0 CHECK (progress BETWEEN 0 AND 100),
                    description TEXT,
                    dependency TEXT DEFAULT '',
                    create_time TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
                    update_time TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
                    FOREIGN KEY (project_id) REFERENCES projects (project_id) ON DELETE CASCADE
                )
                ''')

                # 3. 操作日志表（operation_logs）：审计追踪
                conn.execute('''
                CREATE TABLE IF NOT EXISTS operation_logs (
                    log_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    operation_type TEXT NOT NULL,
                    operation_desc TEXT NOT NULL,
                    operate_time TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
                    project_id INTEGER,
                    task_id INTEGER,
                    FOREIGN KEY (project_id) REFERENCES projects (project_id) ON DELETE SET NULL,
                    FOREIGN KEY (task_id) REFERENCES tasks (task_id) ON DELETE SET NULL
                )
                ''')

                conn.commit()
                logger.info(f"Windows 数据库初始化完成（路径：{DatabaseHandler.DB_PATH}）")
            except sqlite3.Error as e:
                logger.error(f"数据库初始化失败：{str(e)}")
                conn.rollback()

    # ---------------------- 项目表（projects）CRUD ----------------------
    def create_project(self, name, start_date, end_date, description=""):
        """创建项目：Windows 中文插入适配"""
        with self.get_connection() as conn:
            try:
                cursor = conn.execute('''
                INSERT INTO projects (project_name, start_date, end_date, description)
                VALUES (?, ?, ?, ?)
                ''', (name, start_date, end_date, description))
                project_id = cursor.lastrowid
                conn.commit()
                self.log_operation("CREATE_PROJECT", f"创建项目：{name}（ID：{project_id}）", project_id=project_id)
                return project_id
            except sqlite3.Error as e:
                logger.error(f"创建项目失败（{name}）：{str(e)}")
                conn.rollback()
                return None

    def get_all_projects(self):
        """获取所有项目：返回元组列表（ID 为整数）"""
        with self.get_connection() as conn:
            try:
                cursor = conn.execute('''
                SELECT project_id, project_name, start_date, end_date, description
                FROM projects
                ORDER BY create_time DESC
                ''')
                # 转换为元组（确保 project_id 是整数）
                projects = [tuple(row) for row in cursor.fetchall()]
                return projects
            except sqlite3.Error as e:
                logger.error(f"获取项目列表失败：{str(e)}")
                return []

    def get_project_by_id(self, project_id):
        """通过 ID 获取项目：ID 强制整数"""
        with self.get_connection() as conn:
            try:
                cursor = conn.execute('''
                SELECT project_id, project_name, start_date, end_date, description
                FROM projects
                WHERE project_id = ?
                ''', (int(project_id),))  # 强制转整数，避免类型不匹配
                project = cursor.fetchone()
                return tuple(project) if project else None
            except sqlite3.Error as e:
                logger.error(f"获取项目详情失败（ID：{project_id}）：{str(e)}")
                return None

    def get_current_project(self):
        """获取默认项目（最新创建的项目）"""
        projects = self.get_all_projects()
        return projects[0] if projects else None

    def update_project(self, project_id, **kwargs):
        """更新项目：过滤有效字段，ID 强制整数"""
        valid_fields = ["name", "start_date", "end_date", "description"]
        update_data = {k: v for k, v in kwargs.items() if k in valid_fields}
        if not update_data:
            logger.warning(f"更新项目失败（ID：{project_id}）：无有效字段")
            return False
        
        # 构建 SQL（适配 Windows 数据库语法）
        set_clause = ", ".join([f"project_{k} = ?" for k in update_data.keys()]) + ", update_time = datetime('now', 'localtime')"
        values = list(update_data.values()) + [int(project_id)]  # ID 转整数
        
        with self.get_connection() as conn:
            try:
                conn.execute(f'''
                UPDATE projects
                SET {set_clause}
                WHERE project_id = ?
                ''', values)
                conn.commit()
                self.log_operation("UPDATE_PROJECT", f"更新项目（ID：{project_id}）：{update_data}", project_id=int(project_id))
                return True
            except sqlite3.Error as e:
                logger.error(f"更新项目失败（ID：{project_id}）：{str(e)}")
                conn.rollback()
                return False

    def delete_project(self, project_id):
        """删除项目：级联删除任务（Windows 外键生效）"""
        with self.get_connection() as conn:
            try:
                project_id_int = int(project_id)
                project = self.get_project_by_id(project_id_int)
                project_name = project[1] if project else "未知项目"
                
                conn.execute('''
                DELETE FROM projects
                WHERE project_id = ?
                ''', (project_id_int,))
                conn.commit()
                self.log_operation("DELETE_PROJECT", f"删除项目：{project_name}（ID：{project_id_int}）", project_id=project_id_int)
                return True
            except sqlite3.Error as e:
                logger.error(f"删除项目失败（ID：{project_id}）：{str(e)}")
                conn.rollback()
                return False

    # ---------------------- 任务表（tasks）CRUD ----------------------
    def create_task(self, project_id, task_name, priority, owner, start_date, end_date, 
                    status="未开始", progress=0, description="", dependency=""):
        """创建任务：项目 ID 强制整数"""
        with self.get_connection() as conn:
            try:
                cursor = conn.execute('''
                INSERT INTO tasks (
                    project_id, task_name, priority, owner, start_date, end_date,
                    status, progress, description, dependency
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', (int(project_id), task_name, priority, owner, start_date, end_date,
                      status, progress, description, dependency))
                task_id = cursor.lastrowid
                conn.commit()
                self.log_operation("CREATE_TASK", f"创建任务：{task_name}（ID：{task_id}）", project_id=int(project_id), task_id=task_id)
                return task_id
            except sqlite3.Error as e:
                logger.error(f"创建任务失败（{task_name}）：{str(e)}")
                conn.rollback()
                return None

    def get_tasks_by_project(self, project_id):
        """通过项目 ID 获取任务：ID 强制整数"""
        with self.get_connection() as conn:
            try:
                cursor = conn.execute('''
                SELECT task_id, task_name, priority, owner, start_date, end_date,
                       status, progress, description, dependency
                FROM tasks
                WHERE project_id = ?
                ORDER BY end_date ASC, create_time DESC
                ''', (int(project_id),))  # 强制转整数
                tasks = [tuple(row) for row in cursor.fetchall()]
                return tasks
            except sqlite3.Error as e:
                logger.error(f"获取项目任务失败（项目ID：{project_id}）：{str(e)}")
                return []

    def update_task(self, task_id, **kwargs):
        """更新任务：ID 强制整数"""
        valid_fields = ["task_name", "priority", "owner", "start_date", "end_date", 
                        "status", "progress", "description", "dependency"]
        update_data = {k: v for k, v in kwargs.items() if k in valid_fields}
        if not update_data:
            logger.warning(f"更新任务失败（ID：{task_id}）：无有效字段")
            return False
        
        set_clause = ", ".join([f"{k} = ?" for k in update_data.keys()]) + ", update_time = datetime('now', 'localtime')"
        values = list(update_data.values()) + [int(task_id)]
        
        with self.get_connection() as conn:
            try:
                # 获取项目 ID（用于日志）
                cursor = conn.execute("SELECT project_id FROM tasks WHERE task_id = ?", (int(task_id),))
                project_row = cursor.fetchone()
                project_id = project_row[0] if project_row else None
                
                conn.execute(f'''
                UPDATE tasks
                SET {set_clause}
                WHERE task_id = ?
                ''', values)
                conn.commit()
                self.log_operation("UPDATE_TASK", f"更新任务（ID：{task_id}）：{update_data}", project_id=project_id, task_id=int(task_id))
                return True
            except sqlite3.Error as e:
                logger.error(f"更新任务失败（ID：{task_id}）：{str(e)}")
                conn.rollback()
                return False

    def batch_update_tasks(self, task_ids, **kwargs):
        """批量更新任务：ID 列表强制整数"""
        if not task_ids or len(task_ids) == 0:
            logger.warning("批量更新任务失败：无任务ID")
            return False
        
        valid_fields = ["priority", "owner", "status", "progress"]
        update_data = {k: v for k, v in kwargs.items() if k in valid_fields}
        if not update_data:
            logger.warning("批量更新任务失败：无有效字段")
            return False
        
        # 构建 SQL（Windows 兼容 IN 语法）
        set_clause = ", ".join([f"{k} = ?" for k in update_data.keys()]) + ", update_time = datetime('now', 'localtime')"
        values = list(update_data.values())
        # 任务 ID 转整数，构建 IN 子句
        task_ids_int = [int(tid) for tid in task_ids]
        in_clause = ", ".join(["?" for _ in task_ids_int])
        values.extend(task_ids_int)
        
        with self.get_connection() as conn:
            try:
                # 获取项目 ID（取第一个任务的项目）
                if task_ids_int:
                    cursor = conn.execute("SELECT project_id FROM tasks WHERE task_id = ?", (task_ids_int[0],))
                    project_row = cursor.fetchone()
                    project_id = project_row[0] if project_row else None
                else:
                    project_id = None
                
                conn.execute(f'''
                UPDATE tasks
                SET {set_clause}
                WHERE task_id IN ({in_clause})
                ''', values)
                conn.commit()
                self.log_operation("BATCH_UPDATE_TASK", f"批量更新任务（数量：{len(task_ids_int)}）：{update_data}", project_id=project_id)
                return True
            except sqlite3.Error as e:
                logger.error(f"批量更新任务失败（IDs：{task_ids}）：{str(e)}")
                conn.rollback()
                return False

    def delete_tasks(self, task_ids):
        """批量删除任务：ID 列表强制整数"""
        if not task_ids or len(task_ids) == 0:
            logger.warning("删除任务失败：无任务ID")
            return False
        
        task_ids_int = [int(tid) for tid in task_ids]
        in_clause = ", ".join(["?" for _ in task_ids_int])
        
        with self.get_connection() as conn:
            try:
                # 获取项目 ID（用于日志）
                project_id = None
                if task_ids_int:
                    cursor = conn.execute("SELECT project_id FROM tasks WHERE task_id = ?", (task_ids_int[0],))
                    project_row = cursor.fetchone()
                    project_id = project_row[0] if project_row else None
                
                conn.execute(f'''
                DELETE FROM tasks
                WHERE task_id IN ({in_clause})
                ''', task_ids_int)
                conn.commit()
                self.log_operation("DELETE_TASK", f"批量删除任务（数量：{len(task_ids_int)}）", project_id=project_id)
                return True
            except sqlite3.Error as e:
                logger.error(f"删除任务失败（IDs：{task_ids}）：{str(e)}")
                conn.rollback()
                return False

    # ---------------------- 操作日志（operation_logs） ----------------------
    def log_operation(self, oper_type, oper_desc, project_id=None, task_id=None):
        """记录操作日志：Windows 中文日志适配"""
        with self.get_connection() as conn:
            try:
                # 处理 None 值（SQLite 兼容）
                proj_id = int(project_id) if project_id is not None else None
                tsk_id = int(task_id) if task_id is not None else None
                
                conn.execute('''
                INSERT INTO operation_logs (operation_type, operation_desc, project_id, task_id)
                VALUES (?, ?, ?, ?)
                ''', (oper_type, oper_desc, proj_id, tsk_id))
                conn.commit()
            except sqlite3.Error as e:
                logger.error(f"记录操作日志失败：{str(e)}（类型：{oper_type}，描述：{oper_desc}）")
                conn.rollback()

# 全局数据库处理实例
db_handler = DatabaseHandler.get_instance()

# 为了保持向后兼容性，导出原来的函数接口
create_project = db_handler.create_project
get_all_projects = db_handler.get_all_projects
get_project_by_id = db_handler.get_project_by_id
get_current_project = db_handler.get_current_project
update_project = db_handler.update_project
delete_project = db_handler.delete_project
create_task = db_handler.create_task
get_tasks_by_project = db_handler.get_tasks_by_project
update_task = db_handler.update_task
batch_update_tasks = db_handler.batch_update_tasks
delete_tasks = db_handler.delete_tasks
log_operation = db_handler.log_operation
